package J9_26.Utils;

import J9_26.empty.Book;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class BookUtils {
    Scanner sc = new Scanner(System.in);
    //实现添加图书功能
    public void addBook() throws Exception {
        System.out.println("....添加图书....");//伪代码
        System.out.println("请输入图书的编号：");
        int bookId = sc.nextInt();
        System.out.println("请输入图书的名称：");
        String bookName = sc.next();
        System.out.println("请输入图书的作者：");
        String bookAuthor = sc.next();
        System.out.println("请输入图书的价格：");
        float bookPrice = sc.nextFloat();

        //构建Book对象
        Book book = new Book(bookId, bookName, bookAuthor, bookPrice);
        Connection conn = Connect.getConnect();

        //获取预编执行语句对象
        String sql = "insert into bookList values(?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置SQL参数
        pstmt.setInt(1, book.getBookId());
        pstmt.setString(2, book.getBookName());
        pstmt.setString(3, book.getAuthor());
        pstmt.setFloat(4, book.getPrice());

        //执行SQL
        int row = pstmt.executeUpdate();
        pstmt.close();
        conn.close();

        //提示
        System.out.println("图书添加成功！");
    }

    //实现修改图书功能
    public void updateBook() throws Exception {
        System.out.println("....修改图书....");//伪代码
        System.out.println("请输入要修改的图书编号：");
        int bookId = sc.nextInt();
        System.out.println("请输入修改后图书的名称：");
        String bookName = sc.next();
        System.out.println("请输入修改后图书的作者：");
        String bookAuthor = sc.next();
        System.out.println("请输入修改后图书的价格：");
        float bookPrice = sc.nextFloat();
        //构建Book对象
        Book book = new Book(bookId, bookName, bookAuthor, bookPrice);
        Connection conn = Connect.getConnect();

        //获取预编执行语句对象
        String sql = "update bookList set  bookName = (?), author = (?), price = (?) where bookId = (?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置SQL参数
        pstmt.setString(1, book.getBookName());
        pstmt.setString(2, book.getAuthor());
        pstmt.setFloat(3, book.getPrice());
        pstmt.setInt(4, book.getBookId());

        //执行SQL
        int row = pstmt.executeUpdate();
        pstmt.close();
        conn.close();

        //提示
        System.out.println("图书修改成功！");
    }

    //实现删除图书功能
    public void deleteBook() throws SQLException {
        System.out.println("....删除图书....");//伪代码
        System.out.println("请输入要删除的图书编号：");
        int bookId = sc.nextInt();

        Connection conn = null;
        try {
            conn = Connect.getConnect();
        } catch (Exception e) {
            e.printStackTrace();
        }

        //获取预编执行语句对象
        String sql = "delete from bookList  where bookId = (?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置SQL参数
        pstmt.setInt(1, bookId);

        //执行SQL
        int row = pstmt.executeUpdate();
        pstmt.close();
        conn.close();

        //提示
        System.out.println("图书删除成功！");
    }

    //实现删除图书功能
    public void selectBook() throws SQLException {
        System.out.println("....查询图书....");//伪代码
        System.out.println("请输入要查询的图书名：");
        String bookName = sc.next();
        Connection conn = null;
        try {
            conn = Connect.getConnect();
        } catch (Exception e) {
            e.printStackTrace();
        }

        //获取预编执行语句对象
        String sql = "select * from bookList where bookName = (?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置SQL参数
        pstmt.setString(1, bookName);

        //执行SQL
        ResultSet resultSet = null;
        resultSet = pstmt.executeQuery();
        if (resultSet.next() == false) {
            System.out.println("未找到该图书");
        } else {
            Book book = new Book(resultSet.getInt("bookId"),
                    resultSet.getString("bookName"),
                    resultSet.getString("author"),
                    resultSet.getFloat("price"));
            book.print();
            System.out.println("图书查询成功！");
        }
        while (resultSet.next()) {

        }
        pstmt.close();
        conn.close();
    }

    public void selectAll() throws SQLException {
        Connection conn = null;
        try {
            conn = Connect.getConnect();
        } catch (Exception e) {
            e.printStackTrace();
        }
        //获取预编执行语句对象
        String sql = "select * from bookList";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //执行SQL
        ResultSet resultSet = null;
        resultSet = pstmt.executeQuery();
        if (resultSet == null) {
            System.out.println("未找到图书");
        }
        while (resultSet.next()) {
            Book book = new Book(resultSet.getInt("bookId"),
                    resultSet.getString("bookName"),
                    resultSet.getString("author"),
                    resultSet.getFloat("price"));
            book.print();
        }
        pstmt.close();
        conn.close();

        //提示
        System.out.println("图书查询成功！");
    }
}
